Nitrates and Water Quality (Part 2: Analyzing & Visualizing Data) Preview CT-STEM

Analyze Data in Google Sheets/Excel

Analyze Data in Google Sheets/Excel to determine if your assigned location has a nitrate pollution problem.

Step 1: Open water quality data in Google Sheets/Excel. Use the data you collected previously, open the attached .xlsx file, or may a copy of the linked Google Sheet.

Step 2: Find the data for your water collection site. Each sheet/tab in the Excel file represents a different water collection site. Search tip: “Ctrl+F” (FIND) “[your site number]"

Site locations (ask your teacher for your assigned site)

Group 1 Turkey River at Garber, IA (# USGS 05412500)
Group 2 Maquoketa River near Green Island, IA (# USGS 05418720)
Group 3 Mississippi River at Clinton, IA (# USGS 05420500)
Group 4 Old Mans Creek near Iowa City, IA (# USGS 05455100)
Group 5 Cedar River at Blairs Ferry Road at Palo, IA (# USGS 05464420)
Group 6 Hoover Creek near 2nd Street at West Branch, IA (# USGS 0546494205)
Group 7 Iowa River at Wapello, IA (# USGS 05465500)
Group 8 Boone River near Webster City, IA 9 (# USGS 05481000)
Group 9 Des Moines River at 2nd Avenue at Des Moines, IA (# USGS 05482000)
Group 10 North Raccoon River near Sac City, IA (# USGS 05482300)
Group 11 North Raccoon River near Jefferson, IA (# USGS 05482500)
Group 12 South Raccoon River at Redfield, IA (# USGS 05484000)
Group 13 Raccoon River at Van Meter, IA (# USGS 05484500)
Group 14 Little Sioux River at 300th St near Spencer, IA (# USGS 06604440)
Group 15 West Nishnabotna River at Randolph, IA (# USGS 06808500)
Group 16 Nodaway River at Clarinda, IA (# USGS 06817000)

Questions

Please answer the questions below.

Question 2.1

Which site location are you analyzing?

Question 2.2

Is your site missing any data from the year? If so, which parts of the year does your data best represent?

Question 2.3

How many days in the past year does your site have data for?

Use a function to count this efficiently.
Option 1: Select a blank cell. Type “=count” into this cell, and click on the COUNT option. Then select the nitrate data again to computationally count the number of days with data at your site.
Option 2: Select a blank cell. Insert -> Function -> Count. Then select the nitrate data again to computationally count the number of days with data at your site.

Question 2.4

How frequently does the nitrate level at your site exceed the health standard?

Select all numeric data representing the “Daily mean of Nitrate plus nitrite, water, in situ, milligrams per liter as nitrogen” (in column D under the cell that says “14n”)

Format -> Conditional Formatting: select cell value is “greater than” “10”, click the “Formatting Style” dropdown to select a color or make bold all value that are above the health limit for nitrate in water.

Type “=countif” into a blank cell, and select the COUNTIF option to count how many days the water at your site exceeded the nitrate health limit of 10mg/L. =“COUNTIF(data range,criteria)” for example “=COUNTIF(D11:D377,">10”)”

Question 2.5

What percent of the time does the nitrate level exceed the health standard?
Calculate the percentage of total days above the health limit using your calculated numbers.

Notes

These notes will appear on every page in this lesson so feel free to put anything here you'd like to keep track of.